﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CdgNetPersistencia.ClasesBases;
using System.Data.SqlClient;
using CdgNetPersistencia.ClasesBases;

namespace CdgNetPersistencia
{
    /// <summary>
    /// Utileria para interaccion con MS SQL Server
    /// </summary>
    public class SQLServerUtiles : ConectorBase
    {

        #region PROPIEDADES DE LA CLASE

        /// <summary>
        /// Nombre de la clase
        /// </summary>
        public const string NOMBRE_CLASE = "SQLServerUtiles";

        /// <summary>
        /// Modo de Autenticacion por Usuario del SO
        /// </summary>
        public const string AUTENTICACION_WINDOWS = "Autenticacion de Windows";

        /// <summary>
        /// Modo de Autenticacion por Login de la BDD
        /// </summary>
        public const string AUTENTICACION_SQLSERVER = "Autenticacion de Sql Server";

        /// <summary>
        /// Arreglo de los Modos de Conexion Disponibles
        /// </summary>
        public static string[] LISTA_MODOS_AUTENTICACIONES = new String[2] { AUTENTICACION_WINDOWS, AUTENTICACION_SQLSERVER };

        /// <summary>
        /// Arreglo de Nombres Comunes de Instancias Locales
        /// </summary>
        public static string[] LISTA_INSTANCIAS_LOCALES = new String[2] { "SQLEXPRESS", "MSSQLSERVER" };

        /// <summary>
        /// Cadena de Conexion para Usuario de SO
        /// </summary>
        private const string CADENA_CONEXION_WINDOWS = "Server={0}; Database={1}; Integrated Security= yes";

        /// <summary>
        /// Cadena de Conexion para Login de SQL Server
        /// </summary>
        private const string CADENA_CONEXION_SQLSERVER = "Data Source={0}; Initial Catalog={1};User Id={2};Password={3}";


        private SqlConnectionStringBuilder __oCadenaConexion;
        private SqlConnection __oConexion;
        private SqlTransaction __oTransaccionSQL;

        /// <summary>
        /// Devuelve o establece el Tipo de Conexion 
        /// </summary>
        public string cTipoConexion { get; set; }


        public enum PARAM_ESPECIALES { TBL_BASE, DATASET };

        #endregion


        #region CONSTRUCTORES DE LA CLASE

        /// <summary>
        /// Constructor de la clase
        /// </summary>
        /// <param name="cServidorParam">Nombre del servidor de base de datos</param>
        /// <param name="cCatalogoParam">Catalogo de base de datos</param>
        /// <param name="cUsuarioParam">ID de Usuario</param>
        /// <param name="cContrasenaParam">Contrasena</param>
        /// <param name="nTiempoComandosParam">Tiempo de espera de respuesta a comandos</param>
        public SQLServerUtiles(string cServidorParam, string cCatalogoParam
                                , string cUsuarioParam, string cContrasenaParam
                                , int nTiempoComandosParam)
            : base(cUsuarioParam, cContrasenaParam, cServidorParam, cCatalogoParam, 1433, string.Empty)
        {
            cTipoConexion = AUTENTICACION_SQLSERVER;

            //formateamos la cadena de conexion
            __oCadenaConexion = new SqlConnectionStringBuilder(string.Format(CADENA_CONEXION_SQLSERVER
                                                                            , _cServidor
                                                                            , _cCatalogo
                                                                            , _cUsuario
                                                                            , _cContrasena)
                                                                );
            nTiempoComandos = nTiempoComandos;

        }

        /// <summary>
        /// Constructor de la clase
        /// </summary>
        /// <param name="cServidorParam">Nombre del servidor de base de datos</param>
        /// <param name="cCatalogoParam">Catalogo de base de datos</param>
        /// <param name="nTiempoComandosParam">Tiempo de espera de respuesta a comandos</param>
        public SQLServerUtiles(string cServidorParam, string cCatalogoParam, int nTiempoComandosParam)
            : base(string.Empty, string.Empty, cServidorParam, cCatalogoParam, 1433, string.Empty)
        {
            cTipoConexion = AUTENTICACION_WINDOWS;

            //formateamos la cadena de conexion
            __oCadenaConexion = new SqlConnectionStringBuilder(string.Format(CADENA_CONEXION_WINDOWS
                                                                , _cServidor
                                                                , _cCatalogo)
                                                            );

            nTiempoComandos = nTiempoComandos;

        }

        #endregion


        #region Miembros de ConectorBase

        /// <summary>
        /// Abre la conexion a la base de datos
        /// </summary>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lConectar()
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lConectar()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //intentamos abrir la conexion a la base de datos
                __oConexion = new SqlConnection(__oCadenaConexion.ConnectionString);

                __oConexion.Open();

                lResultado = new List<object>() { 1, "Ok" };

            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;

        }

        /// <summary>
        /// Cierra la conexion activa
        /// </summary>
        /// <param name="sender">Objeto que efectua la llamada al metodo</param>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lDesconectar(object sender)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lDesconectar()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //si la conexion esta abierta, la intentamos cerrar la conexion a la base de datos
                if (__oConexion.State == System.Data.ConnectionState.Open) __oConexion.Close();

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Ejecuta la sentencia sql
        /// </summary>
        /// <param name="cSentenciaSQL">Sentencia SQL a ejecutar</param>
        /// <param name="aParametros">Arreglo de valores parametros</param>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lEjecutar_sentencia(string cSentenciaSQL, object[] aValores)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_sentencia()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //objetos a utilizar
                SqlCommand oComandoSQL = new SqlCommand(string.Format(cSentenciaSQL, aValores), __oConexion);

                //configuramos el comando
                oComandoSQL.CommandTimeout = nTiempoComandos;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                if (__oTransaccionSQL != null) oComandoSQL.Transaction = __oTransaccionSQL;

                _Mostrar_SQL(oComandoSQL.CommandText);

                oComandoSQL.ExecuteNonQuery();

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (SqlException ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message, ex };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message, ex };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Ejecuta la consulta sql y devuelve un DataTable como segundo elemento
        /// </summary>
        /// <param name="cConsultaSQL">Consulta SQL a ejecutar</param>
        /// <param name="aParametros">Arreglo de valores parametros</param>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lEjecutar_consulta(string cConsultaSQL, object[] aValores)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_consulta()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //objetos a utilizar
                SqlCommand oComandoSQL = new SqlCommand(string.Format(cConsultaSQL, aValores), __oConexion);

                //configuramos el comando
                oComandoSQL.CommandTimeout = nTiempoComandos;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                if (__oTransaccionSQL != null) oComandoSQL.Transaction = __oTransaccionSQL;

                //DataTableMapping tmMapeo = new DataTableMapping();

                SqlDataAdapter daAdaptador = new SqlDataAdapter(oComandoSQL);
                DataTable dtTabla = new DataTable();

                _Mostrar_SQL(oComandoSQL.CommandText);

                daAdaptador.Fill(dtTabla);

                //si hay filas devueltas
                if (dtTabla.Rows.Count > 0) lResultado = new List<object>() { 1, dtTabla, daAdaptador };
                else lResultado = new List<object>() { 0, ConectorBase.ERROR_NO_HAY_FILAS };

            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Ejecuta la consulta sql y devuelve un unico objeto valor
        /// </summary>
        /// <param name="cConsultaSQL">Consulta SQL a ejecutar</param>
        /// <param name="aParametros">Arreglo de valores parametros</param>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lEjecutar_escalar(string cConsultaSQL, object[] aValores)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_escalar()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //objetos a utilizar
                SqlCommand oComandoSQL = new SqlCommand(string.Format(cConsultaSQL, aValores), __oConexion);

                //configuramos el comando
                oComandoSQL.CommandTimeout = nTiempoComandos;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                if (__oTransaccionSQL != null) oComandoSQL.Transaction = __oTransaccionSQL;

                _Mostrar_SQL(oComandoSQL.CommandText);

                object oValor = oComandoSQL.ExecuteScalar();

                lResultado = new List<object>() { 1, oValor };

            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Ejecuta el procedimiento almacenado y devuelve una lista de resultado
        /// </summary>
        /// <param name="cProcedimiento">Procedimiento Almacenado a ejecutar</param>
        /// <param name="aParametros">Arreglo de valores parametros</param>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lEjecutar_procedimiento(string cProcedimiento, object[] aValores)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_sp_parametros()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //objetos a utilizar
                SqlCommand oComandoSQL = new SqlCommand(string.Format(cProcedimiento, aValores), __oConexion);

                //configuramos el comando
                oComandoSQL.CommandTimeout = nTiempoComandos;
                oComandoSQL.CommandType = CommandType.StoredProcedure;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                if (__oTransaccionSQL != null) oComandoSQL.Transaction = __oTransaccionSQL;

                _Mostrar_SQL(oComandoSQL.CommandText);

                object oValor = oComandoSQL.ExecuteNonQuery();

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Establece el metodo de Autoconfirmacion de transacciones
        /// </summary>
        /// <param name="bParam">Valor a asignar</param>
        public override void Set_autocommit(bool bParam)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Inicia una transaccion
        /// </summary>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lIniciar_transaccion()
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lIniciar_transaccion()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //si hay una transaccion activa
                if (__oTransaccionSQL != null)
                    //generamos un error
                    throw new Exception("Hay una transaccion pendiente! \n Confirmela o reviertala antes de iniciar otra...");

                //iniciamos una transaccion
                __oTransaccionSQL = __oConexion.BeginTransaction();

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Confirma una transaccion activa
        /// </summary>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lConfirmar_transaccion()
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lConfirmar_transaccion()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //confirmamos la transaccion
                __oTransaccionSQL.Commit();

                //la liberamos
                __oTransaccionSQL.Dispose();
                __oTransaccionSQL = null;

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Revierte una transaccion activa
        /// </summary>
        /// <returns>Lista de Resultados</returns>
        public override List<object> lRevertir_transaccion()
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lConectar()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //si existe una transaccion activa
                if (__oTransaccionSQL != null)
                {
                    //revertimos la transaccion
                    __oTransaccionSQL.Rollback();

                    //la liberamos
                    __oTransaccionSQL.Dispose();
                    __oTransaccionSQL = null;
                }

                //establecemos el resultado del metodo
                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }



        #endregion


        #region METODOS ESPECIFICOS

        /// <summary>
        /// Ejecuta una consulta y devuelve un datatable y su adaptador de datos
        /// </summary>
        /// <param name="cConsultaSQL">Consulta SQL a ejecutar</param>
        /// <param name="dicParametros">Diccionario de Parametros [string, object]</param>
        /// <returns>Lista de resultados [int, object, object]</returns>
        public List<object> lEjecutar_consulta(string cConsultaSQL, Dictionary<string, object> dicParametros)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_consulta()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //objetos a utilizar
                SqlCommand oComandoSQL = new SqlCommand(cConsultaSQL, __oConexion);

                //configuramos el comando
                oComandoSQL.CommandTimeout = nTiempoComandos;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                if (__oTransaccionSQL != null) oComandoSQL.Transaction = __oTransaccionSQL;

                SqlDataAdapter daAdaptador = new SqlDataAdapter(oComandoSQL);
                DataTable dtTabla = new DataTable();

                _Mostrar_SQL(oComandoSQL.CommandText);

                //si se paso la instancia de la tabla y su dataset
                if (dicParametros.Keys.Contains(PARAM_ESPECIALES.TBL_BASE.ToString())
                        && dicParametros.Keys.Contains(PARAM_ESPECIALES.DATASET.ToString()))
                {
                    //tomamos la instancia TBL parametro
                    TBLbase oTBLbase = (TBLbase)dicParametros[PARAM_ESPECIALES.TBL_BASE.ToString()];

                    //nombre del mapeo
                    //string cNombreMap = string.Format("map{0}", oTBLbase.Get_nombre_tabla());
                    string cNombreMap = oTBLbase.GetNombreTabla;

                    //creamos una instancia de mapeo de tablas
                    System.Data.Common.DataTableMapping tmMapeo = new System.Data.Common.DataTableMapping(
                                                                    oTBLbase.GetNombreTabla
                                                                    , cNombreMap
                                                                    );

                    //lo agregamos al adaptador
                    daAdaptador.TableMappings.Add(tmMapeo);

                    //recuperamos los datos
                    daAdaptador.Fill((DataSet)dicParametros[PARAM_ESPECIALES.DATASET.ToString()], cNombreMap);

                }
                else
                {
                    //sino, normalmente
                    daAdaptador.Fill(dtTabla);
                }

                //si hay filas devueltas
                if (dtTabla.Rows.Count > 0) lResultado = new List<object>() { 1, dtTabla, daAdaptador };
                else lResultado = new List<object>() { 0, ConectorBase.ERROR_NO_HAY_FILAS };

            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message };
            }

            //devolvemos el resultado del metodo
            return lResultado;

        }

        /// <summary>
        /// Ejecuta una sentencia sql pero a partir de una instancia de Comando
        /// </summary>
        /// <param name="cmdComandoSQL">Instancia del Comando a ejecutar</param>
        /// <returns>Lista de resultados List[int, object]</returns>
        public List<object> lEjecutar_sentencia(SqlCommand cmdComandoSQL)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_sentencia()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //configuramos el comando
                cmdComandoSQL.Connection = __oConexion;
                cmdComandoSQL.CommandTimeout = nTiempoComandos;

                //si hay una transaccion activa, asignamos el comando como parte de la misma
                //if (__oTransaccionSQL != null)
                cmdComandoSQL.Transaction = __oTransaccionSQL;

                _Mostrar_SQL(cmdComandoSQL.CommandText);

                cmdComandoSQL.ExecuteNonQuery();

                lResultado = new List<object>() { 1, "Ok" };
            }
            catch (SqlException ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message, ex };
            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message, ex };
            }

            //devolvemos el resultado del metodo
            return lResultado;
        }

        /// <summary>
        /// Ejecuta una insercion masiva desde el datatable parametro a la tabla especificada
        /// los campos y orden de los mismos deben de coincidir
        /// </summary>
        /// <param name="dtDataTableParam">DataTable origen de datos</param>
        /// <param name="cTablaDestinoParam">Nombre tabla destino</param>
        /// <returns>Lista de resultados List[int, object]</returns>
        public List<object> lEjecutar_bulkInsert(DataTable dtDataTableParam
                                                    , string cTablaDestinoParam)
        {
            string NOMBRE_METODO = NOMBRE_CLASE + ".lEjecutar_bulkInsert()";
            List<object> lResultado = new List<object>() { 0, NOMBRE_METODO + " No Ejecutado." };

            try
            {
                //configuramos el comando
                SqlBulkCopy bulkCopy = new SqlBulkCopy(__oConexion, SqlBulkCopyOptions.CheckConstraints, __oTransaccionSQL);

                bulkCopy.BulkCopyTimeout = nTiempoComandos;
                bulkCopy.DestinationTableName = cTablaDestinoParam;
                bulkCopy.BatchSize = 100;
                bulkCopy.NotifyAfter = 100;

                //ejecutamos el comando
                bulkCopy.WriteToServer(dtDataTableParam);

                //resultado del metodo
                lResultado = new List<object>() { 1, "Ok" };

            }
            catch (Exception ex)
            {
                //en caso de error
                lResultado = new List<object>() { -1, NOMBRE_METODO + ": " + ex.Message, ex };
            }

            //devolvemos el resultado del metodo
            return lResultado;

        }

        /// <summary>
        /// Devuelve una referencia a la conexion misma a la base de datos
        /// </summary>
        public SqlConnection Get_conector
        {
            get
            {
                //si la conexion no esta abierta actualmente, la intentamos abrir
                if (__oConexion.State != ConnectionState.Open) lConectar();

                //devolvemos la conexion
                return __oConexion;
            }
        }



        #endregion

    }
}
